<?php
require_once('../../../includes/classes/core.php');

$agent	= $_REQUEST['agent'];
$queue	= $_REQUEST['queuet'];
$start_time = $_REQUEST['start_time'];
$end_time 	= $_REQUEST['end_time'];

$start_time = $db->convertDateTime($start_time);
$end_time = $db->convertDateTime($end_time);

$ress =mysql_query("SELECT 	crst.`name` as name,
								COUNT(*) AS zarebi,
								(SELECT 	COUNT(*)
									FROM 	incomming_call
									left JOIN 	asterisk_incomming ON incomming_call.uniqueid=asterisk_incomming.uniqueid
									LEFT JOIN 	crystal_users ON incomming_call.user_id=crystal_users.id
									WHERE 	crystal_users.`name`= crst.`name`
									AND `incomming_call`.`date` BETWEEN '$start_time' AND '$end_time' 
 									AND `incomming_call`.`phone`!=''
 									AND `incomming_call`.`source_id`!=''
								)AS damushavebuli,
								(SELECT 	COUNT(*)
									FROM 	asterisk_outgoing
									LEFT JOIN 	crystal_users as cr ON asterisk_outgoing.user_id=cr.id
									WHERE 	cr.`name`= crst.`name`
									AND `asterisk_outgoing`.`call_datetime` BETWEEN '$start_time' AND '$end_time'
								)AS gamavali,
	 							(SELECT  SEC_TO_TIME(ROUND(SUM(TIMESTAMPDIFF(SECOND,task.planned_end_date,task.fact_end_date))/COUNT(*),0))
									FROM 	task
									left JOIN  crystal_users ON task.responsible_user_id=crystal_users.id
									WHERE crystal_users.`name`=crst.`name`
									AND  `task`.`date` >= '$start_time' 
									AND  `task`.`date` <= '$end_time' 
									AND  `task`.`status` = 3
									)AS shes_time,
								(SELECT SEC_TO_TIME(sum(TIMESTAMPDIFF(SECOND,
										IF(person_work_graphic.`start`< '$start_time','$start_time', person_work_graphic.`start`),
										IF(person_work_graphic.`end`  > '$end_time', '$end_time', person_work_graphic.`end`))))
									FROM 	person_work_graphic
									LEFT JOIN 	crystal_users ON person_work_graphic.person_id = crystal_users.id 										
									WHERE  	crystal_users.`name` = crst.`name` 
									AND  	person_work_graphic.actived=1 AND crystal_users.actived=1										
									AND   	(person_work_graphic.`start` >= '$start_time' OR 	person_work_graphic.`end` 	>= '$start_time')
									AND   	(person_work_graphic.`start` <= '$end_time' OR  person_work_graphic.`end` 	<= '$end_time')
									)AS gegmiuri,
								(SELECT COUNT(*)
									FROM 	task
									left JOIN  crystal_users ON task.responsible_user_id=crystal_users.id
									WHERE crystal_users.`name` = crst.`name`
									AND  `task`.`date` >= '$start_time' 
									AND  `task`.`date` <= '$end_time' 
									AND  `task`.`status` = 3) AS task_count,
								(SELECT 	COUNT(*)
									FROM 	task
									LEFT JOIN 	crystal_users as crs ON task.responsible_user_id1=crs.id
									WHERE 	task.task_type_id=1 AND task.status=3 AND crs.`name`= crst.`name`
									AND task.`date` BETWEEN '$start_time' AND '$end_time'
								)AS gamavali_damushavebuli,
						ROUND((COUNT(*)/(SELECT 	COUNT(*)
										FROM 	asterisk_incomming
										LEFT JOIN   	crystal_users AS crist ON asterisk_incomming.user_id = crist.id
										WHERE 	crist.`name` IN($agent)
										AND `asterisk_incomming`.`call_datetime` BETWEEN '$start_time' AND '$end_time'
										) *100), 2) AS `percent`,
								SEC_TO_TIME(SUM(asterisk_incomming.duration)) AS saubris_dro,
								SEC_TO_TIME(ROUND((SUM(asterisk_incomming.duration)/COUNT(*)),0)) AS sashualo,
								SEC_TO_TIME(MAX(asterisk_incomming.duration)) AS max_time,
								SEC_TO_TIME(MIN(asterisk_incomming.duration)) AS min_time
						FROM asterisk_incomming
						LEFT JOIN crystal_users AS crst ON asterisk_incomming.user_id = crst.id
						WHERE crst.`name` IN($agent)
						AND `asterisk_incomming`.`call_datetime` BETWEEN '$start_time' AND '$end_time'
						AND asterisk_incomming.duration>0
						GROUP BY crst.`name`");
$number = mysql_num_rows($ress) +2;

while($row = mysql_fetch_assoc($ress)){
	
	
	$daumushavebeli_gamavali = $row[gamavali]-$row[gamavali_damushavebuli];
	if ($row[zarebi]<$row[damushavebuli]){
		$daumushavebeli_zarebi	= $row[zarebi]-$row[zarebi];
		$row[damushavebuli]=$row[zarebi];
			
	}else
	{
		$daumushavebeli_zarebi = $row[zarebi]-$row[damushavebuli];
	};
	
	$coeficienti			 = round($row[damushavebuli]/$row[zarebi],2);
	if ($coeficienti>1){$coeficienti=1;}else{$coeficienti=round($row[damushavebuli]/$row[zarebi],2);};
	$dat .= '
						<ss:Row>
							<ss:Cell>
								<ss:Data ss:Type="String">'.$row[name].'</ss:Data>
							</ss:Cell>
							<ss:Cell>
								<ss:Data ss:Type="String">'.$row[gegmiuri].'</ss:Data>
							</ss:Cell>
							<ss:Cell>
								<ss:Data ss:Type="String">'.$row[zarebi].'</ss:Data>
							</ss:Cell>
							<ss:Cell>
								<ss:Data ss:Type="String">'.$row[damushavebuli].'</ss:Data>
							</ss:Cell>							
							<ss:Cell>
								<ss:Data ss:Type="String">'.$daumushavebeli_zarebi.'</ss:Data>
							</ss:Cell>
							<ss:Cell>
								<ss:Data ss:Type="String">'.$row[percent].' %</ss:Data>
							</ss:Cell>
							<ss:Cell>
								<ss:Data ss:Type="String">'.$row[saubris_dro].'</ss:Data>
							</ss:Cell>
							<ss:Cell>
								<ss:Data ss:Type="String">'.$row[sashualo].'</ss:Data>
							</ss:Cell>
							<ss:Cell>
								<ss:Data ss:Type="String">'.$row[max_time].'</ss:Data>
							</ss:Cell>
							<ss:Cell>
								<ss:Data ss:Type="String">'.$row[min_time].'</ss:Data>
							</ss:Cell>
							<ss:Cell>
								<ss:Data ss:Type="String">'.$coeficienti.'</ss:Data>
							</ss:Cell>
							<ss:Cell>
								<ss:Data ss:Type="String">'.$row[task_count].'</ss:Data>
							</ss:Cell>
							<ss:Cell>
								<ss:Data ss:Type="String">'.$row[shes_time].'</ss:Data>
							</ss:Cell>
							<ss:Cell>
								<ss:Data ss:Type="String">'.$row[gamavali].'</ss:Data>
							</ss:Cell>
							<ss:Cell>
								<ss:Data ss:Type="String">'.$row[gamavali_damushavebuli].'</ss:Data>
							</ss:Cell>
							<ss:Cell>
								<ss:Data ss:Type="String">'.$daumushavebeli_gamavali.'</ss:Data>
							</ss:Cell>
						</ss:Row>';
}
	$name = "ნაპასუხები ზარები ოპერატორების მიხედვით";



$data = '
<?xml version="1.0" encoding="utf-8"?><?mso-application progid="Excel.Sheet"?>
<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:o="urn:schemas-microsoft-com:office:office">
	<o:DocumentProperties>
		<o:Title>'.$name.'</o:Title>
	</o:DocumentProperties>
	<ss:ExcelWorkbook>
		<ss:WindowHeight>9000</ss:WindowHeight>
		<ss:WindowWidth>50000</ss:WindowWidth>
		<ss:ProtectStructure>false</ss:ProtectStructure>
		<ss:ProtectWindows>false</ss:ProtectWindows>
	</ss:ExcelWorkbook>
	<ss:Styles>
		<ss:Style ss:ID="Default">
			<ss:Alignment ss:Vertical="Center" ss:Horizontal="Center" ss:WrapText="1" />
			<ss:Font ss:FontName="Sylfaen" ss:Size="12" />
			<ss:Interior />
			<ss:NumberFormat />
			<ss:Protection />
			<ss:Borders>
				<ss:Border ss:Position="Top" ss:Color="#000000" ss:Weight="1" ss:LineStyle="Continuous" />
				<ss:Border ss:Position="Bottom" ss:Color="#000000" ss:Weight="1" ss:LineStyle="Continuous" />
				<ss:Border ss:Position="Left" ss:Color="#000000" ss:Weight="1" ss:LineStyle="Continuous" />
				<ss:Border ss:Position="Right" ss:Color="#000000" ss:Weight="1" ss:LineStyle="Continuous" />
			</ss:Borders>
		</ss:Style>
		<ss:Style ss:ID="title">
			<ss:Borders />
			<ss:NumberFormat ss:Format="@" />
			<ss:Alignment ss:WrapText="1" ss:Horizontal="Center" ss:Vertical="Center" />
		</ss:Style>
		<ss:Style ss:ID="headercell">
			<ss:Font ss:Bold="1" />
			<ss:Interior ss:Pattern="Solid" />
			<ss:Alignment ss:WrapText="1" ss:Horizontal="Center" ss:Vertical="Center" />
		</ss:Style>
		<ss:Style ss:ID="headercellRotated">
			<ss:Font ss:Bold="1" />
			<ss:Interior ss:Pattern="Solid" />
			<ss:Alignment ss:WrapText="1" ss:Horizontal="Center" ss:Rotate="90" ss:Vertical="Center" />
		</ss:Style>
	</ss:Styles>
	<ss:Worksheet ss:Name="'.$number.'">
		<ss:Names>
			<ss:NamedRange ss:Name="Print_Titles" ss:RefersTo="=\' '.$number.' \'!R1:R2" />
		</ss:Names>
		<ss:Table x:FullRows="1" x:FullColumns="1" ss:ExpandedColumnCount="16" ss:ExpandedRowCount="'.$number.'">
			<ss:Column ss:AutoFitWidth="1" ss:Width="100" />
			<ss:Column ss:AutoFitWidth="1" ss:Width="100" />
			<ss:Column ss:AutoFitWidth="1" ss:Width="100" />
			<ss:Column ss:AutoFitWidth="1" ss:Width="100" />
			<ss:Column ss:AutoFitWidth="1" ss:Width="100" />
			<ss:Column ss:AutoFitWidth="1" ss:Width="100" />
			<ss:Column ss:AutoFitWidth="1" ss:Width="100" />
			<ss:Column ss:AutoFitWidth="1" ss:Width="100" />
			<ss:Column ss:AutoFitWidth="1" ss:Width="100" />
			<ss:Column ss:AutoFitWidth="1" ss:Width="100" />
			<ss:Column ss:AutoFitWidth="1" ss:Width="100" />
			<ss:Column ss:AutoFitWidth="1" ss:Width="100" />
			<ss:Column ss:AutoFitWidth="1" ss:Width="100" />
			<ss:Column ss:AutoFitWidth="1" ss:Width="100" />
			<ss:Column ss:AutoFitWidth="1" ss:Width="100" />
			<ss:Column ss:AutoFitWidth="1" ss:Width="100" />
			<ss:Row ss:Height="30">
				<ss:Cell ss:StyleID="title" ss:MergeAcross="15">
					<ss:Data xmlns:html="http://www.w3.org/TR/REC-html40" ss:Type="String">
						<html:B>
							<html:Font html:Size="14">'.$name.'</html:Font>
						</html:B>
					</ss:Data>
					<ss:NamedCell ss:Name="Print_Titles" />
				</ss:Cell>
			</ss:Row>
			<ss:Row ss:AutoFitHeight="1" ss:Height="25">
				<ss:Cell ss:StyleID="headercell">
					<ss:Data ss:Type="String">ოპერატორი</ss:Data>
					<ss:NamedCell ss:Name="Print_Titles" />
				</ss:Cell>
				<ss:Cell ss:StyleID="headercell">
					<ss:Data ss:Type="String">გეგმიური სთ</ss:Data>
					<ss:NamedCell ss:Name="Print_Titles" />
				</ss:Cell>
				<ss:Cell ss:StyleID="headercell">
					<ss:Data ss:Type="String">მიღებული ზარები</ss:Data>
					<ss:NamedCell ss:Name="Print_Titles" />
				</ss:Cell>
				<ss:Cell ss:StyleID="headercell">
					<ss:Data ss:Type="String">დამუშავებული</ss:Data>
					<ss:NamedCell ss:Name="Print_Titles" />
				</ss:Cell>				
				<ss:Cell ss:StyleID="headercell">
					<ss:Data ss:Type="String">დაუმუშავებელი</ss:Data>
					<ss:NamedCell ss:Name="Print_Titles" />
				</ss:Cell>
				<ss:Cell ss:StyleID="headercell">
					<ss:Data ss:Type="String">% ნაპასუხები</ss:Data>
					<ss:NamedCell ss:Name="Print_Titles" />
				</ss:Cell>
				<ss:Cell ss:StyleID="headercell">
					<ss:Data ss:Type="String">ზარის დრო</ss:Data>
					<ss:NamedCell ss:Name="Print_Titles" />
				</ss:Cell>
				<ss:Cell ss:StyleID="headercell">
					<ss:Data ss:Type="String">საშუა.ზარის ხანგრძლ.</ss:Data>
					<ss:NamedCell ss:Name="Print_Titles" />
				</ss:Cell>
				<ss:Cell ss:StyleID="headercell">
					<ss:Data ss:Type="String">ზარის მაქს.დრო</ss:Data>
					<ss:NamedCell ss:Name="Print_Titles" />
				</ss:Cell>
				<ss:Cell ss:StyleID="headercell">
					<ss:Data ss:Type="String">ზარის მინ.დრო</ss:Data>
					<ss:NamedCell ss:Name="Print_Titles" />
				</ss:Cell>
				<ss:Cell ss:StyleID="headercell">
					<ss:Data ss:Type="String">კოეფ. მაქს(1)</ss:Data>
					<ss:NamedCell ss:Name="Print_Titles" />
				</ss:Cell>
				<ss:Cell ss:StyleID="headercell">
					<ss:Data ss:Type="String">შესრულებული დავალება</ss:Data>
					<ss:NamedCell ss:Name="Print_Titles" />
				</ss:Cell>
				<ss:Cell ss:StyleID="headercell">
					<ss:Data ss:Type="String">შესრულების საშ.დრო</ss:Data>
					<ss:NamedCell ss:Name="Print_Titles" />
				</ss:Cell>
				<ss:Cell ss:StyleID="headercell">
					<ss:Data ss:Type="String">გამავალი ზარი</ss:Data>
					<ss:NamedCell ss:Name="Print_Titles" />
				</ss:Cell>
			    <ss:Cell ss:StyleID="headercell">
					<ss:Data ss:Type="String">დამუშავებული</ss:Data>
					<ss:NamedCell ss:Name="Print_Titles" />
				</ss:Cell>
				<ss:Cell ss:StyleID="headercell">
					<ss:Data ss:Type="String">დაუმუშავებელი</ss:Data>
					<ss:NamedCell ss:Name="Print_Titles" />
				</ss:Cell>
			</ss:Row>
		
'; 
$data .= $dat; 
  
		
$data .='</ss:Table>
		<x:WorksheetOptions>
			<x:PageSetup>
				<x:Layout x:CenterHorizontal="1" x:Orientation="Portrait" />
				<x:Header x:Data="&amp;R&#10;&#10;&amp;D" />
				<x:Footer x:Data="Page &amp;P of &amp;N" x:Margin="0.5" />
				<x:PageMargins x:Top="0.5" x:Right="0.5" x:Left="0.5" x:Bottom="0.8" />
			</x:PageSetup>
			<x:FitToPage />
			<x:Print>
				<x:PrintErrors>Blank</x:PrintErrors>
				<x:FitWidth>1</x:FitWidth>
				<x:FitHeight>32767</x:FitHeight>
				<x:ValidPrinterInfo />
				<x:VerticalResolution>1000</x:VerticalResolution>
			</x:Print>
			<x:Selected />
			<x:DoNotDisplayGridlines />
			<x:ProtectObjects>False</x:ProtectObjects>
			<x:ProtectScenarios>False</x:ProtectScenarios>
		</x:WorksheetOptions>
	</ss:Worksheet>
</ss:Workbook>
		';

if($number == '2'){
	$null= 1;
	echo json_encode($null);
}else{
	echo json_encode($data);
}
file_put_contents('excel.xls', $data);



	